﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Maticsoft.DBUtility;
using System.Data.SqlClient;

namespace ElingOA6_Admin_DSJCJ.DAL
{
    /// <summary>
    /// ChkInfoCls数据类
    /// </summary>
    public class ChkInfoCls
    {
        /// <summary>
        /// 构造函数
        /// </summary>
        public ChkInfoCls() 
        { }
        /// <summary>
        /// 获得数据列表
        /// </summary>
        /// <creator>chenly</creator>
        /// <date>2013-7-26</date>
        /// <param name="strWhere">查询条件</param>
        /// <returns>DataSet表</returns>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select FYear,FInfoClsNo,FPreInfoClsNo,FInfoClsName,FStdScore,FChkType,FEntityNo ");
            strSql.Append(" FROM t_chkInfoCls ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        /// <param name="fYear">年度</param>
        /// <param name="fInfoClsNo">栏目编号</param>
        /// <param name="fEntityNo">公司编号</param>

        public void Delete(decimal fYear, string fInfoClsNo, string fEntityNo)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from t_chkInfoCls ");
            strSql.Append(" where FYear=@FYear and FInfoClsNo=@FInfoClsNo and FEntityNo='" + fEntityNo + "'");
            SqlParameter[] parameters = {
					new SqlParameter("@FYear", SqlDbType.Decimal),
					new SqlParameter("@FInfoClsNo", SqlDbType.NVarChar,50)};
            parameters[0].Value = fYear;
            parameters[1].Value = fInfoClsNo;


            DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 获取所有的年份
        /// <creator>zhongyl</creator>
        /// <date>2013-10-16</date>
        /// </summary>
        /// <param name="strEntityNo"></param>
        /// <returns></returns>
        public DataSet GetYeat(string strEntityNo)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" SELECT FYear FROM [dbo].[t_chkInfoCls] GROUP BY FYear");
            SqlParameter[] parameters = {
					new SqlParameter("@FEntityNo", SqlDbType.NVarChar,50)};
            parameters[0].Value = strEntityNo;

            return DbHelperSQL.Query(strSql.ToString(), parameters);

        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        /// <creator>chenly</creator>
        /// <date>2013-7-26</date>
        /// <param name="model">一个对象</param>
        public void Add(Model.ChkInfoCls model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into t_chkInfoCls(");
            strSql.Append("FYear,FInfoClsNo,FPreInfoClsNo,FInfoClsName,FStdScore,FChkType,FEntityNo,FisExamine)");
            strSql.Append(" values (");
            strSql.Append("@FYear,@FInfoClsNo,@FPreInfoClsNo,@FInfoClsName,@FStdScore,@FChkType,@FEntityNo,@FisExamine)");
            SqlParameter[] parameters = {
					new SqlParameter("@FYear", SqlDbType.Decimal,5),
					new SqlParameter("@FInfoClsNo", SqlDbType.NVarChar,20),
					new SqlParameter("@FPreInfoClsNo", SqlDbType.NVarChar,20),
					new SqlParameter("@FInfoClsName", SqlDbType.NVarChar,20),
					new SqlParameter("@FStdScore", SqlDbType.Decimal,9),
					new SqlParameter("@FChkType", SqlDbType.Char,1),
					new SqlParameter("@FEntityNo", SqlDbType.NVarChar,12),
                    new SqlParameter("@FisExamine",SqlDbType.Char,1)};
            parameters[0].Value = model.FYear;
            parameters[1].Value = model.FInfoClsNo;
            if (model.FPreInfoClsNo == "")
            {
                parameters[2].Value = DBNull.Value;
            }
            else {
                parameters[2].Value = model.FPreInfoClsNo;
            }           
            parameters[3].Value = model.FInfoClsName;
            parameters[4].Value = model.FStdScore;
            parameters[5].Value = model.FChkType;
            parameters[6].Value = model.FEntityNo;
            parameters[7].Value = model.FisExamine;
            DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 判断是否存在
        /// </summary>
        /// <creator>chenly</creator>
        /// <date>2013-7-26</date>
        /// <param name="fYear">年度</param>
        /// <param name="fPreInfoClsNo">上级栏目编号</param>
        /// <param name="fEntityNo">公司编号</param>
        /// <returns>是否存在</returns>
        public bool Exists(decimal fYear, string fInfoClsNo, string fEntityNo)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from t_chkInfoCls");
            strSql.Append(" where FYear=@FYear and FInfoClsNo=@FInfoClsNo and FEntityNo='" + fEntityNo + "'");
            SqlParameter[] parameters = {
					new SqlParameter("@FYear", SqlDbType.Decimal),
					
					new SqlParameter("@FInfoClsNo", SqlDbType.NVarChar,50)};
            parameters[0].Value = fYear;

            parameters[1].Value = fInfoClsNo;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        /// <creator>chenly</creator>
        /// <date>2013-7-27</date>
        /// <param name="fYear">年度</param>
        /// <param name="fInfoClsNo">上级栏目编号</param>
        /// <param name="fEntityNo">公司编号</param>
        /// <returns>一个实体对象</returns>
        public Model.ChkInfoCls GetModel(decimal fYear, string fInfoClsNo, string fEntityNo)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 FYear,FInfoClsNo,FPreInfoClsNo,FInfoClsName,FStdScore,FChkType,FisExamine from t_chkInfoCls ");
            strSql.Append(" where FYear=@FYear and FInfoClsNo=@FInfoClsNo and FEntityNo='" + fEntityNo + "'");
            SqlParameter[] parameters = {
					new SqlParameter("@FYear", SqlDbType.Decimal),
					new SqlParameter("@FInfoClsNo", SqlDbType.NVarChar,50)
					};
            parameters[0].Value = fYear;
            parameters[1].Value = fInfoClsNo;


            Model.ChkInfoCls model = new Model.ChkInfoCls();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["FYear"].ToString() != "")
                {
                    model.FYear = decimal.Parse(ds.Tables[0].Rows[0]["FYear"].ToString());
                }
                model.FInfoClsNo = ds.Tables[0].Rows[0]["FInfoClsNo"].ToString();
                model.FPreInfoClsNo = ds.Tables[0].Rows[0]["FPreInfoClsNo"].ToString();
                model.FInfoClsName = ds.Tables[0].Rows[0]["FInfoClsName"].ToString();
                if (ds.Tables[0].Rows[0]["FStdScore"].ToString() != "")
                {
                    model.FStdScore = decimal.Parse(ds.Tables[0].Rows[0]["FStdScore"].ToString());
                }
                model.FChkType = ds.Tables[0].Rows[0]["FChkType"].ToString();
                model.FisExamine = ds.Tables[0].Rows[0]["FisExamine"].ToString();
                return model;
            }
            else
            {
                return null;
            }
        }


        /// <summary>
        /// 更新一条数据
        /// </summary>
        /// <creator>chenly</creator>
        /// <date>2013-7-26</date>
        /// <param name="model">model对象</param>
        public void Update(Model.ChkInfoCls model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update t_chkInfoCls set ");
            strSql.Append("FInfoClsName=@FInfoClsName,");
            strSql.Append("FStdScore=@FStdScore,");
            strSql.Append("FChkType=@FChkType,");
            strSql.Append("FisExamine=@FisExamine");
            strSql.Append(" where FYear=@FYear and FInfoClsNo=@FInfoClsNo  and FEntityNo=@FEntityNo ");
            SqlParameter[] parameters = {
					new SqlParameter("@FYear", SqlDbType.Decimal,5),
					new SqlParameter("@FInfoClsNo", SqlDbType.NVarChar,20),
                    //new SqlParameter("@FPreInfoClsNo", SqlDbType.NVarChar,20),
					new SqlParameter("@FInfoClsName", SqlDbType.NVarChar,20),
					new SqlParameter("@FStdScore", SqlDbType.Decimal,9),
					new SqlParameter("@FChkType", SqlDbType.Char,1),
					new SqlParameter("@FEntityNo", SqlDbType.NVarChar,12),
                    new SqlParameter("@FisExamine",SqlDbType.Char,1)};
            parameters[0].Value = model.FYear;
            parameters[1].Value = model.FInfoClsNo;

            parameters[2].Value = model.FInfoClsName;
            parameters[3].Value = model.FStdScore;
            parameters[4].Value = model.FChkType;
            parameters[5].Value = model.FEntityNo;
            parameters[6].Value = model.FisExamine;
            DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 获得数据列表
        /// <creator>sunlj</creator>
        /// <date>2013-10-16</date>
        /// </summary>
        /// <param name="strEntityNo">单位编号</param>
        /// <param name="decYear">年度</param>
        /// <returns></returns>
        public DataSet GetListByYear(string strEntityNo, decimal decYear)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select FYear,FInfoClsNo,FPreInfoClsNo,FInfoClsName,FStdScore,FChkType,FEntityNo,FisExamine ");
            strSql.Append(" FROM t_chkInfoCls where  FYear=@FYear and FEntityNo=@FEntityNo");
            SqlParameter[] parameters = {
                    new SqlParameter("@FYear", SqlDbType.Decimal),				
                    new SqlParameter("@FEntityNo", SqlDbType.NVarChar,50)};
            parameters[0].Value = decYear;
            parameters[1].Value = strEntityNo;
            return DbHelperSQL.Query(strSql.ToString(), parameters);
        }

    }
}
